<?php
/**
 * SemesterTeacherCourse Model
 *
 * 教師配課
 *
 * @version $Id: SemesterTeacherCourse.php 73 2010-04-25 00:30:44Z yingkuang.chen@gmail.com $
 * @license New BSD License
 * @uses Semester_Model_SemesterTeacherCourseMapper
 * @package semester
 * @subpackage Model
 */
class Semester_Model_SemesterTeacherCourse extends
Semester_Model_Doctrine_BaseSemesterTeacherCourse
{

    /**
     * 學期計分科目
     * @var boolean
     */
    const SCORED = true;


    /**
     * 取本學期所有教師配課數
     * @param integer|string $year
     * @param integer|null $semester
     * @return array
     */
    public function fetchTeacherCourseCount($year,$semester=null)
    {
        if($semester==null){
            $arr = explode("-",$year);
            $year = (int)$arr[0];
            $semester = (int)$arr[1];
        }

        $q = $this->getTable()->createQuery('a')
        ->select('a.teacher_sn, SUM(number) AS cc, b.name AS name')
        ->leftJoin('a.Teacher_Model_TeacherView b')
        ->where('a.year=?',$year)
        ->andwhere('a.semester=?',$semester)
        ->groupBy('a.teacher_sn')
        ->orderBy('b.current_kind_sn');
        return $q->fetchArray();
    }

    /**
     * 列出本學期班級配課情形
     * @param integer|string $year
     * @param integer|null $semester
     * @param integer  $grade
     * @param integer  $classSn
     * @return array
     */
    function fetchClassCourseCount($year,$semester=null,$grade, $classSn)
    {

        if($semester==null){
            $arr = explode("-",$year);
            $year = (int)$arr[0];
            $semester = (int)$arr[1];
        }

        $q = $this->getTable()->createQuery('a')
        ->select('a.*, b.name AS name, c.name AS teacher_name')
        ->leftJoin('a.Semester_Model_SemesterCourse b')
        ->leftJoin('a.Teacher_Model_TeacherView c')
        ->where('a.year=?',$year)
        ->andwhere('a.semester=?',$semester)
        ->andwhere('a.grade=?',$grade)
        ->andwhere('a.class_sn=?',$classSn)
        ->orderBy('a.teacher_sn ,b.rank');
        return $q->fetchArray();
    }


    /**
     * 取得教師本學期配課列表
     * @param integer|string $year
     * @param integer|null $semester
     * @param integer $teacherSn
     * @param boolean $scored default false
     * @return array
     */
    function getTeacherCourse($year,$semester=null,$teacherSn,$scored=false)
    {
        if($semester==null){
            $arr = explode("-",$year);
            $year = (int)$arr[0];
            $semester = (int)$arr[1];
        }
        $q = $this->getTable()->createQuery('a')
        ->select('a.*, b.scope AS scope,b.name AS name ,b.scored_mode AS scored_mode,
        b.sections AS sections ,b.rate AS rate,b.tutor_revisable AS tutor_revisable')
        ->leftJoin('a.Semester_Model_SemesterCourseSubjectView b')
        ->where('a.year=?',$year)
        ->andwhere('a.semester=?',$semester)
        ->andwhere('a.teacher_sn=?',$teacherSn)
        ->andwhere('b.enable=?','y')
        ->orderby('b.scope_rank , b.rank');
        if ($scored)
        $q->andwhere('b.scored=?','y');
        // 班級陣列
        $semesterClass = new Semester_Model_SemesterClass;
        $classArr = $semesterClass->getClassNames($year,$semester);

        $arr = array();
        foreach($q->fetchArray() as $val) {
            $val['className'] = $classArr[$val['grade'].'-'.$val['class_sn']];
            $arr[$val['sn']] = $val;
        }
        // 分組班資料
        $q =$this->getTable()->createQuery('a')
        ->select('a.sn, b.group_name as name')
        ->leftJoin('a.Student_Model_StudentScoreElectiveGroup b')
        ->where('a.sn = b.teacher_course_sn')
        ->andWhere('a.year=?', $year)
        ->andwhere('a.semester=?',$semester)
        ->andwhere('a.teacher_sn=?',$teacherSn);
        //  echo $q->getSqlQuery();
        foreach ($q->fetchArray() as $val) {
            $temp = $arr[$val['sn']];
            $temp['className'] = $temp['name'];
            $temp['name'] = $val['name'];
            $temp['sn'] = 'group-'.$val['sn'];
            unset($arr[$val['sn']])  ;
            $arr[$temp['sn']] = $temp;
        }

        return $arr;

    }

    /**
     * 科目列表
     * @param integer|string $year
     * @param integer|null $semester
     * @param integer|null  $grade
     * @param integer|null $classSn
     * @return array
     */
    function fetchCourseList($year,$semester=null,$grade=null, $classSn=null)
    {
        if($semester==null){
            $arr = explode("-",$year);
            $year = (int)$arr[0];
            $semester = (int)$arr[1];
        }

        $q = Doctrine_Query::create()
        ->from('Semester_Model_SemesterCourseSubjectView')
        ->where("enable='Y'")
        ->andwhere('year=?',$year)
        ->andwhere('semester=?',$semester)
        ->andwhere('grade=?',$grade)
        ->andwhere('class_sn=?',$classSn)
        ->orderBy('rank');

        $res =  $q->fetchArray();
        if (count($res)==0){
            $q = Doctrine_Query::create()
            ->from('Semester_Model_SemesterCourseSubjectView')
            ->where("enable='Y'")
            ->andwhere('year=?',$year)
            ->andwhere('semester=?',$semester)
            ->andwhere('grade=?',$grade)
            ->andwhere('class_sn is NULL')
            ->orderBy('rank');
            $res = $q->fetchArray();
        }

        return $res;
    }

    /**
     *  一個班級應排課程
     * @param integer  $year
     * @param integer $semester
     * @param integer $grade
     * @param integer $classSn
     * @return array
     */
    public function getClassData($year,$semester,$grade,$classSn)
    {

        $q = $this->getTable()->createQuery('a')
        ->select('a.*, b.name AS subject_name, c.name AS teacher_name')
        ->leftJoin('a.Semester_Model_SemesterCourse b')
        ->leftJoin('a.Teacher_Model_TeacherView c')
        ->where('a.year=?',$year)
        ->andwhere('a.semester=?',$semester)
        ->andwhere('a.grade=?',$grade)
        ->andwhere('a.class_sn=?',$classSn);

        return $q->fetchArray();
    }
}
